//simplewebserver package
//
//This file implements connecting to MySQL
//and some convenient methods to handle MySQL
package simplewebserver

import (
    "fmt"
    "log"
    "time"
    "database/sql"
    "github.com/go-sql-driver/mysql"
)

//MySQL user model
type User struct{
    name string
    pwd []byte  //encrypted pwd
    email string
    age int
    birth time.Time
}

func (u User) String() string {
	return fmt.Sprintf("User: [%s,%s,%d,%s]",
        u.name, u.email, u.age,
        u.birth.Format("2006-01-02"))
}

type MyDB struct{
    db *sql.DB
}

//NewUser new a User model
//
//note: here arg pwd is the raw text, not encrypted
//birth string's layout is 2006-01-02
func NewUser(name, pwd, email string,
    age int, birth string) User{
    enp, _ := Encrypt([]byte(pwd))
    b, _ := time.Parse("2006-01-02", birth)
    return User{name, enp, email, age, b}
}

func ConnMySQL() (MyDB, error){
    con, err := sql.Open("mysql", "admin:admin@/test")
    return MyDB{con}, err
}

func (c MyDB) Close(){
    c.db.Close()
}

func fatal(err error){
    if err != nil {
        log.Fatal(err)
    }
}

func QueryAll() []User{
    con, err := ConnMySQL()
    defer con.Close()
    fatal(err)
    rows, err := con.db.Query("SELECT * FROM user")
    fatal(err)
    defer rows.Close()
    var users []User
    for rows.Next() {
        var name string
        var pwd []byte
        var email sql.NullString
        var age int
        var birth mysql.NullTime
        fatal(rows.Scan(&name, &pwd, &email, &age, &birth))
        users = append(users, User{name:name, pwd:pwd,
        email:email.String, age:age, birth:birth.Time})
    }
    fatal(rows.Err())
    return users
}

//Login uses name and pwd to login
//
//note: here arg pwd is the raw text, not encrypted
func Login(name string, pwd string) (bool, error){
    con, err := ConnMySQL()
    defer con.Close()
    fatal(err)
    var p []byte
    err = con.db.QueryRow(
        "SELECT pwd FROM user where name=?",
        name).Scan(&p)
    if err == sql.ErrNoRows {
        return false, err
    }
    fatal(err)
    enp, err := Encrypt([]byte(pwd))
    if err != nil {
        return false, err
    }
    return string(p) == string(enp), nil
}

func ExistUser(name string) bool {
    con, err := ConnMySQL()
    defer con.Close()
    fatal(err)
    var n string
    err = con.db.QueryRow(
        "SELECT name FROM user where name=?",
        name).Scan(&n)
    if err == sql.ErrNoRows {
        return false
    }
    fatal(err)
    return n == name
}

func FindUser(n string) User {
    con, err := ConnMySQL()
    defer con.Close()
    fatal(err)
    var name string
    var email sql.NullString
    var age int
    var birth mysql.NullTime
    err = con.db.QueryRow(
        `SELECT name, email, age, birth FROM
        user where name=?`,
        n).Scan(&name, &email, &age, &birth)
    if err == sql.ErrNoRows {
        return User{}
    }
    fatal(err)
    return NewUser(name, "", email.String, age,
        birth.Time.Format("2006-01-02"))
}

func AddUser(u User) error{
    con, err := ConnMySQL()
    defer con.Close()
    fatal(err)
    _, err = con.db.Exec(`INSERT INTO user (name,
        pwd, email, age, birth) VALUES (?, ?, ?,
        ?, ?)`, u.name, u.pwd, u.email, u.age,
        u.birth.Format("2006-01-02"))
    return err
}

func EditUser(name string, u User) error{
    con, err := ConnMySQL()
    defer con.Close()
    fatal(err)
    _, err = con.db.Exec(`UPDATE user SET name=?, email=?,
        age=?, birth=? WHERE name=?`, u.name, u.email, u.age,
        u.birth.Format("2006-01-02"), name)
    return err
}

//SetPwd uses name and pwd to set a new password
//
//note: here arg pwd is the raw text, not encrypted
func SetPwd(name string, pwd string) error{
    con, err := ConnMySQL()
    defer con.Close()
    fatal(err)
    enp, err := Encrypt([]byte(pwd))
    if err != nil {
        return err
    }
    _, err = con.db.Exec(`UPDATE user SET pwd=?
        WHERE name=?`, enp, name)
    return err
}